﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VipSys.SystemEntity;
using System.Data;
using VipSys.Common.Library;
using System.Diagnostics;

namespace VipSys.DataLayer
{
    public class UserAccess
    {
        private static UserAccess m_Instance = null;
        /// <summary>
        /// 单实例
        /// </summary>
        public static UserAccess Instance
        {
            get
            {
                if (m_Instance == null)
                    m_Instance = new UserAccess();
                return m_Instance;
            }
        }

        public short Add(User user)
        {

            string szFiled = string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17}"
                , SystemData.UserTable.Baks
                , SystemData.UserTable.ExamPlace
                , SystemData.UserTable.ExamSchool
                , SystemData.UserTable.EmployeeID
                , SystemData.UserTable.Gender
                , SystemData.UserTable.Hotel
                , SystemData.UserTable.HotelExpense
                , SystemData.UserTable.MoneyBack
                , SystemData.UserTable.Name
                , SystemData.UserTable.PassWord
                , SystemData.UserTable.PayMoney
                , SystemData.UserTable.Room
                , SystemData.UserTable.School
                , SystemData.UserTable.Sequence
                , SystemData.UserTable.Tel
                , SystemData.UserTable.ExceptRoomie
                , SystemData.UserTable.Template
                , SystemData.UserTable.PayPlace);
            string szValue = string.Format("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}'"
                , user.Bak
                , user.ExamPlace
                , user.ExamSchool
                , user.EmployeeID
                , user.Gender
                , user.Hotel
                , user.HotelExpense
                , user.MoneyBack
                , user.Name
                , user.PassWord
                , user.PayMoney
                , user.Room
                , user.School
                , user.Sequence
                , user.Tel
                , user.ExceptRoomie
                , user.Template
                , user.PayPlace);
            string szSql = string.Format("Insert Into {0}({1}) values({2})"
                , SystemData.DBTable.USER
                , szFiled
                , szValue);
            try
            {
                int result = SystemParam.Instance.DBAccess.ExecuteNonQuery(szSql);
                if (result <= 0)
                    return SystemData.ReturnValue.EXCEPTION;
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        /// <summary>
        /// 导入时通过手机号作为唯一标示进行编辑
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public short EditByTel(User user)
        {
            if (string.IsNullOrEmpty(user.Tel))
                return SystemData.ReturnValue.OK;
            string szFiledValue = string.Format("{0}='{1}',{2}='{3}',{4}='{5}',{6}='{7}',{8}='{9}',{10}='{11}',{12}='{13}',{14}='{15}',{16}='{17}',{18}='{19}',{20}='{21}',{22}='{23}',{24}='{25}',{26}='{27}',{28}='{29}',{30}='{31}',{32}='{33}',{34}='{35}',{36}='{37}'"
                , SystemData.UserTable.Baks, user.Bak
                , SystemData.UserTable.ExamPlace, user.ExamPlace
                , SystemData.UserTable.ExamSchool, user.ExamSchool
                , SystemData.UserTable.Gender, user.Gender
                , SystemData.UserTable.Hotel, user.Hotel
                , SystemData.UserTable.HotelExpense, user.HotelExpense
                , SystemData.UserTable.MoneyBack, user.MoneyBack
                , SystemData.UserTable.Name, user.Name
                , SystemData.UserTable.PassWord, user.PassWord
                , SystemData.UserTable.PayMoney, user.PayMoney
                , SystemData.UserTable.Room, user.Room
                , SystemData.UserTable.Sequence, user.Sequence
                , SystemData.UserTable.Tel, user.Tel
                , SystemData.UserTable.EmployeeID, user.EmployeeID
                , SystemData.UserTable.School, user.School
                , SystemData.UserTable.ExceptRoomie, user.ExceptRoomie
                , SystemData.UserTable.Template, user.Template
                , SystemData.UserTable.PayPlace, user.PayPlace
                , SystemData.UserTable.Status, SystemData.Status.NORMAL);
           
                string szCondition = string.Format(" {0}='{1}'"
                    
                    , SystemData.UserTable.Tel, user.Tel);
                string szSql = string.Format("update {0} set {1} where {2}"
                    , SystemData.DBTable.USER
                    , szFiledValue
                    , szCondition);
            
            try
            {
                int result = SystemParam.Instance.DBAccess.ExecuteNonQuery(szSql);
                if (result <= 0)
                    return SystemData.ReturnValue.EXCEPTION;
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        public short Edit(User user)
        {
            string szFiledValue = string.Format("{0}='{1}',{2}='{3}',{4}='{5}',{6}='{7}',{8}='{9}',{10}='{11}',{12}='{13}',{14}='{15}',{16}='{17}',{18}='{19}',{20}='{21}',{22}='{23}',{24}='{25}',{26}='{27}',{28}='{29}',{30}='{31}',{32}='{33}',{34}='{35}',{36}='{37}'"
                , SystemData.UserTable.Baks, user.Bak
                , SystemData.UserTable.ExamPlace, user.ExamPlace
                , SystemData.UserTable.ExamSchool, user.ExamSchool
                , SystemData.UserTable.Gender, user.Gender
                , SystemData.UserTable.Hotel, user.Hotel
                , SystemData.UserTable.HotelExpense, user.HotelExpense
                , SystemData.UserTable.MoneyBack, user.MoneyBack
                , SystemData.UserTable.Name, user.Name
                , SystemData.UserTable.PassWord, user.PassWord
                , SystemData.UserTable.PayMoney, user.PayMoney
                , SystemData.UserTable.Room, user.Room
                , SystemData.UserTable.Sequence, user.Sequence
                , SystemData.UserTable.Tel, user.Tel
                , SystemData.UserTable.EmployeeID, user.EmployeeID
                , SystemData.UserTable.School, user.School
                , SystemData.UserTable.ExceptRoomie, user.ExceptRoomie
                , SystemData.UserTable.Template, user.Template
                , SystemData.UserTable.PayPlace, user.PayPlace
                , SystemData.UserTable.Status, SystemData.Status.NORMAL);
            string szCondition = string.Format(" {0}={1}"
                , SystemData.UserTable.ID, user.ID);
            string szSql = string.Format("update {0} set {1} where {2}"
                , SystemData.DBTable.USER
                , szFiledValue
                , szCondition);
            try
            {
                int result = SystemParam.Instance.DBAccess.ExecuteNonQuery(szSql);
                if (result <= 0)
                    return SystemData.ReturnValue.EXCEPTION;
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short Del(User user)
        {
            string szCondition = string.Format(" {0}={1}"
                , SystemData.UserTable.ID, user.ID);
            string szSql = string.Format("update {0} set {1}='{2}' where {3}"
                , SystemData.DBTable.USER
                , SystemData.UserTable.Status
                ,"0"
                , szCondition);
            try
            {
                int result = SystemParam.Instance.DBAccess.ExecuteNonQuery(szSql);
                if (result <= 0)
                    return SystemData.ReturnValue.EXCEPTION;
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short GetUserPageList(string szName,string szEmployeeID,string szRoleType,int start,int limit, ref List<User> lstUser, string szSchools)
        {
            
            if (lstUser == null)
                lstUser = new List<User>();
            lstUser.Clear();
            string szField=string.Format("*");
            string szCondition=string.Format("1=1 and (status <>'0' or status is null)");
            if(!string.IsNullOrEmpty(szSchools) &&szRoleType!=SystemData.RoleType.SCHOOL)
            {
                szCondition=string.Format("{0} and {1} in ({2})"
                    , szCondition
                    , SystemData.UserTable.School
                    , szSchools);
            }
            if (!string.IsNullOrEmpty(szName))
            {
                szCondition = string.Format("{0} and {1} like'%{2}%'"
                    , szCondition
                    , SystemData.UserTable.Name
                    , szName);  
            }
            if (!string.IsNullOrEmpty(szEmployeeID))
            {
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.UserTable.EmployeeID
                    , szEmployeeID); 
            }
            string szSQL = string.Format("select {0} from {1} where {2}"
                , szField
                , SystemData.DBTable.USER
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSQL);
                for (int i = start; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (i >= (start + limit))
                        break;
                    User user = new User();
                    user.ID = int.Parse(ds.Tables[0].Rows[i][SystemData.UserTable.ID].ToString());
                    user.Bak = ds.Tables[0].Rows[i][SystemData.UserTable.Baks].ToString();
                    user.ExamPlace = ds.Tables[0].Rows[i][SystemData.UserTable.ExamPlace].ToString();
                    user.ExamSchool = ds.Tables[0].Rows[i][SystemData.UserTable.ExamSchool].ToString();
                    user.Gender = ds.Tables[0].Rows[i][SystemData.UserTable.Gender].ToString();
                    user.Hotel = ds.Tables[0].Rows[i][SystemData.UserTable.Hotel].ToString();
                    user.HotelExpense = ds.Tables[0].Rows[i][SystemData.UserTable.HotelExpense].ToString();
                    user.MoneyBack = ds.Tables[0].Rows[i][SystemData.UserTable.MoneyBack].ToString();
                    user.Name = ds.Tables[0].Rows[i][SystemData.UserTable.Name].ToString();
                    user.PassWord = ds.Tables[0].Rows[i][SystemData.UserTable.PassWord].ToString();
                    user.PayMoney = ds.Tables[0].Rows[i][SystemData.UserTable.PayMoney].ToString();
                    user.Room = ds.Tables[0].Rows[i][SystemData.UserTable.Room].ToString();
                    user.School = ds.Tables[0].Rows[i][SystemData.UserTable.School].ToString();
                    user.Sequence = ds.Tables[0].Rows[i][SystemData.UserTable.Sequence].ToString();
                    user.Tel = ds.Tables[0].Rows[i][SystemData.UserTable.Tel].ToString();
                    user.EmployeeID = ds.Tables[0].Rows[i][SystemData.UserTable.EmployeeID].ToString();
                    user.ExceptRoomie = ds.Tables[0].Rows[i][SystemData.UserTable.ExceptRoomie].ToString();
                    user.Template = ds.Tables[0].Rows[i][SystemData.UserTable.Template].ToString();
                    user.PayPlace = ds.Tables[0].Rows[i][SystemData.UserTable.PayPlace].ToString();
                    if (!string.IsNullOrEmpty(user.EmployeeID) && user.EmployeeID != "0")
                    {
                        Employee employee = new Employee();
                        employee.ID = int.Parse(user.EmployeeID);
                        EmployeeAccess.Instance.GetEmployee(employee, ref employee);
                        user.EmployeeName = employee.Name;
                    }
                    lstUser.Add(user);
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        public short GetTotalCount(string szName,string szEmployeeID,string szID, ref int totalCount, string szSchools)
        {
            string szCondition = string.Format("1=1 and (status <>'0' or status is null)");
            if (!string.IsNullOrEmpty(szSchools))
                szCondition = string.Format("{0} and school in ({1}) "
                    , szCondition
                    , szSchools
                    , SystemData.UserTable.EmployeeID
                    , szID);
            if (!string.IsNullOrEmpty(szEmployeeID))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.UserTable.EmployeeID, szEmployeeID);
            if(!string.IsNullOrEmpty(szName))
                szCondition = string.Format("{0} and {1} like '%{2}%'"
                    , szCondition
                    , SystemData.UserTable.Name, szName);
            string szSql = string.Format("select count(*) from {0} where  {1}"
                , SystemData.DBTable.USER
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                totalCount = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short GetUserList(User query, ref List<User> lstUser, string szSchools)
        {
            if (lstUser == null)
                lstUser = new List<User>();
            lstUser.Clear();
            string szField = string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18}"
                 , SystemData.UserTable.Baks
                , SystemData.UserTable.ExamPlace
                , SystemData.UserTable.ExamSchool
                , SystemData.UserTable.Gender
                , SystemData.UserTable.Hotel
                , SystemData.UserTable.HotelExpense
                , SystemData.UserTable.ID
                , SystemData.UserTable.MoneyBack
                , SystemData.UserTable.Name
                , SystemData.UserTable.PassWord
                , SystemData.UserTable.PayMoney
                , SystemData.UserTable.Room
                , SystemData.UserTable.School
                , SystemData.UserTable.Sequence
                , SystemData.UserTable.Tel
                , SystemData.UserTable.EmployeeID
                , SystemData.UserTable.ExceptRoomie
                , SystemData.UserTable.Template
                , SystemData.UserTable.PayPlace);
            string szCondition = string.Format("1=1 and (status <>'0' or status is null) ");
            if (!string.IsNullOrEmpty(szSchools))
                szCondition = string.Format("{0} and {1} in ({2})"
                    , szCondition
                    , SystemData.UserTable.School
                    , szSchools);
            if (!string.IsNullOrEmpty(query.EmployeeID))
                szCondition = string.Format("{0} and {1} ='{2}'"
                    , szCondition
                    , SystemData.UserTable.EmployeeID
                    , query.EmployeeID);
            if (!string.IsNullOrEmpty(query.Hotel))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.UserTable.Hotel, query.Hotel.ToString());
            if (!string.IsNullOrEmpty(query.Room))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.UserTable.Room, query.Room.ToString());
            string szSql = string.Format("select {0} from {1} where {2}"
                , szField
                , SystemData.DBTable.USER
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    User user = new User();
                    user.ID = int.Parse(ds.Tables[0].Rows[i][SystemData.UserTable.ID].ToString());
                    user.Bak = ds.Tables[0].Rows[i][SystemData.UserTable.Baks].ToString();
                    user.ExamPlace = ds.Tables[0].Rows[i][SystemData.UserTable.ExamPlace].ToString();
                    user.ExamSchool = ds.Tables[0].Rows[i][SystemData.UserTable.ExamSchool].ToString();
                    user.Gender = ds.Tables[0].Rows[i][SystemData.UserTable.Gender].ToString();
                    user.Hotel = ds.Tables[0].Rows[i][SystemData.UserTable.Hotel].ToString();
                    user.HotelExpense = ds.Tables[0].Rows[i][SystemData.UserTable.HotelExpense].ToString();
                    user.MoneyBack = ds.Tables[0].Rows[i][SystemData.UserTable.MoneyBack].ToString();
                    user.Name = ds.Tables[0].Rows[i][SystemData.UserTable.Name].ToString();
                    user.PassWord = ds.Tables[0].Rows[i][SystemData.UserTable.PassWord].ToString();
                    user.PayMoney = ds.Tables[0].Rows[i][SystemData.UserTable.PayMoney].ToString();
                    user.Room = ds.Tables[0].Rows[i][SystemData.UserTable.Room].ToString();
                    user.School = ds.Tables[0].Rows[i][SystemData.UserTable.School].ToString();
                    user.Sequence = ds.Tables[0].Rows[i][SystemData.UserTable.Sequence].ToString();
                    user.Tel = ds.Tables[0].Rows[i][SystemData.UserTable.Tel].ToString();
                    user.EmployeeID = ds.Tables[0].Rows[i][SystemData.UserTable.EmployeeID].ToString();
                    user.ExceptRoomie = ds.Tables[0].Rows[i][SystemData.UserTable.ExceptRoomie].ToString();
                    user.Template = ds.Tables[0].Rows[i][SystemData.UserTable.Template].ToString();
                    user.PayPlace = ds.Tables[0].Rows[i][SystemData.UserTable.PayPlace].ToString();
                    if (!string.IsNullOrEmpty(user.EmployeeID))
                    {
                        Employee employee = new Employee();
                        employee.ID = int.Parse(user.EmployeeID);
                        EmployeeAccess.Instance.GetEmployee(employee, ref employee);
                        user.EmployeeName = employee.Name;
                    }
                    lstUser.Add(user);
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }

        public short GetUserList(string szName,string szEmployeeID, ref List<User> lstUser, string szSchools)
        {
            if (lstUser == null)
                lstUser = new List<User>();
            lstUser.Clear();
            string szField = string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18}"
                 , SystemData.UserTable.Baks
                , SystemData.UserTable.ExamPlace
                , SystemData.UserTable.ExamSchool
                , SystemData.UserTable.Gender
                , SystemData.UserTable.Hotel
                , SystemData.UserTable.HotelExpense
                , SystemData.UserTable.ID
                , SystemData.UserTable.MoneyBack
                , SystemData.UserTable.Name
                , SystemData.UserTable.PassWord
                , SystemData.UserTable.PayMoney
                , SystemData.UserTable.Room
                , SystemData.UserTable.School
                , SystemData.UserTable.Sequence
                , SystemData.UserTable.Tel
                , SystemData.UserTable.EmployeeID
                , SystemData.UserTable.ExceptRoomie
                , SystemData.UserTable.Template
                , SystemData.UserTable.PayPlace);
            string szCondition = string.Format("1=1 and (status <>'0' or status is null) ");
            if (!string.IsNullOrEmpty(szSchools))
                szCondition = string.Format("{0} and {1} in ({2})"
                    , szCondition
                    , SystemData.UserTable.School
                    , szSchools);
            if (!string.IsNullOrEmpty(szEmployeeID))
                szCondition = string.Format("{0} and {1} ='{2}'"
                    , szCondition
                    , SystemData.UserTable.EmployeeID
                    , szEmployeeID);
            if (!string.IsNullOrEmpty(szName))
                szCondition = string.Format("{0} and {1} like '%{2}%'"
                    , szCondition
                    , SystemData.UserTable.Name
                    , szName);
            string szSql = string.Format("select {0} from {1} where {2}"
                , szField
                , SystemData.DBTable.USER
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    User user = new User();
                    user.ID = int.Parse(ds.Tables[0].Rows[i][SystemData.UserTable.ID].ToString());
                    user.Bak = ds.Tables[0].Rows[i][SystemData.UserTable.Baks].ToString();
                    user.ExamPlace = ds.Tables[0].Rows[i][SystemData.UserTable.ExamPlace].ToString();
                    user.ExamSchool = ds.Tables[0].Rows[i][SystemData.UserTable.ExamSchool].ToString();
                    user.Gender = ds.Tables[0].Rows[i][SystemData.UserTable.Gender].ToString();
                    user.Hotel = ds.Tables[0].Rows[i][SystemData.UserTable.Hotel].ToString();
                    user.HotelExpense = ds.Tables[0].Rows[i][SystemData.UserTable.HotelExpense].ToString();
                    user.MoneyBack = ds.Tables[0].Rows[i][SystemData.UserTable.MoneyBack].ToString();
                    user.Name = ds.Tables[0].Rows[i][SystemData.UserTable.Name].ToString();
                    user.PassWord = ds.Tables[0].Rows[i][SystemData.UserTable.PassWord].ToString();
                    user.PayMoney = ds.Tables[0].Rows[i][SystemData.UserTable.PayMoney].ToString();
                    user.Room = ds.Tables[0].Rows[i][SystemData.UserTable.Room].ToString();
                    user.School = ds.Tables[0].Rows[i][SystemData.UserTable.School].ToString();
                    user.Sequence = ds.Tables[0].Rows[i][SystemData.UserTable.Sequence].ToString();
                    user.Tel = ds.Tables[0].Rows[i][SystemData.UserTable.Tel].ToString();
                    user.EmployeeID = ds.Tables[0].Rows[i][SystemData.UserTable.EmployeeID].ToString();
                    user.ExceptRoomie = ds.Tables[0].Rows[i][SystemData.UserTable.ExceptRoomie].ToString();
                    user.Template = ds.Tables[0].Rows[i][SystemData.UserTable.Template].ToString();
                    user.PayPlace = ds.Tables[0].Rows[i][SystemData.UserTable.PayPlace].ToString();
                    
                    if (!string.IsNullOrEmpty(user.EmployeeID))
                    {
                        Employee employee = new Employee();
                        employee.ID = int.Parse(user.EmployeeID);
                        EmployeeAccess.Instance.GetEmployee(employee, ref employee);
                        user.EmployeeName = employee.Name;
                    }
                    lstUser.Add(user);
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        /// <summary>
        /// 获得室友
        /// </summary>
        /// <param name="szRoom"></param>
        /// <param name="szHotel"></param>
        /// <param name="lstUser"></param>
        /// <returns></returns>
        public short GetUserList(string szRoom, string szHotel, ref List<User> lstUser)
        {
            if (lstUser == null)
                lstUser = new List<User>();
            lstUser.Clear();
            if (string.IsNullOrEmpty(szRoom) || string.IsNullOrEmpty(szHotel))
                return SystemData.ReturnValue.PARAM_ERROR;
            string szField = string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18}"
                 , SystemData.UserTable.Baks
                , SystemData.UserTable.ExamPlace
                , SystemData.UserTable.ExamSchool
                , SystemData.UserTable.Gender
                , SystemData.UserTable.Hotel
                , SystemData.UserTable.HotelExpense
                , SystemData.UserTable.ID
                , SystemData.UserTable.MoneyBack
                , SystemData.UserTable.Name
                , SystemData.UserTable.PassWord
                , SystemData.UserTable.PayMoney
                , SystemData.UserTable.Room
                , SystemData.UserTable.School
                , SystemData.UserTable.Sequence
                , SystemData.UserTable.Tel
                , SystemData.UserTable.EmployeeID
                , SystemData.UserTable.ExceptRoomie
                , SystemData.UserTable.Template
                , SystemData.UserTable.PayPlace);
            string szCondition = string.Format("1=1 and (status <>'0' or status is null)");

            szCondition = string.Format("{0} and {1} = '{2}'"
                , szCondition
                , SystemData.UserTable.Room
                , szRoom);

            szCondition = string.Format("{0} and {1} ='{2}'"
                , szCondition
                , SystemData.UserTable.Hotel
                , szHotel);

            string szSql = string.Format("select {0} from {1} where  {2}"
                , szField
                , SystemData.DBTable.USER
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    User user = new User();
                    user.ID = int.Parse(ds.Tables[0].Rows[i][SystemData.UserTable.ID].ToString());
                    user.Bak = ds.Tables[0].Rows[i][SystemData.UserTable.Baks].ToString();
                    user.ExamPlace = ds.Tables[0].Rows[i][SystemData.UserTable.ExamPlace].ToString();
                    user.ExamSchool = ds.Tables[0].Rows[i][SystemData.UserTable.ExamSchool].ToString();
                    user.Gender = ds.Tables[0].Rows[i][SystemData.UserTable.Gender].ToString();
                    user.Hotel = ds.Tables[0].Rows[i][SystemData.UserTable.Hotel].ToString();
                    user.HotelExpense = ds.Tables[0].Rows[i][SystemData.UserTable.HotelExpense].ToString();
                    user.MoneyBack = ds.Tables[0].Rows[i][SystemData.UserTable.MoneyBack].ToString();
                    user.Name = ds.Tables[0].Rows[i][SystemData.UserTable.Name].ToString();
                    user.PassWord = ds.Tables[0].Rows[i][SystemData.UserTable.PassWord].ToString();
                    user.PayMoney = ds.Tables[0].Rows[i][SystemData.UserTable.PayMoney].ToString();
                    user.Room = ds.Tables[0].Rows[i][SystemData.UserTable.Room].ToString();
                    user.School = ds.Tables[0].Rows[i][SystemData.UserTable.School].ToString();
                    user.Sequence = ds.Tables[0].Rows[i][SystemData.UserTable.Sequence].ToString();
                    user.Tel = ds.Tables[0].Rows[i][SystemData.UserTable.Tel].ToString();
                    user.EmployeeID = ds.Tables[0].Rows[i][SystemData.UserTable.EmployeeID].ToString();
                    user.ExceptRoomie = ds.Tables[0].Rows[i][SystemData.UserTable.ExceptRoomie].ToString();
                    user.Template = ds.Tables[0].Rows[i][SystemData.UserTable.Template].ToString();
                    user.PayPlace = ds.Tables[0].Rows[i][SystemData.UserTable.PayPlace].ToString();
                    if (!string.IsNullOrEmpty(user.EmployeeID))
                    {
                        Employee employee = new Employee();
                        employee.ID = int.Parse(user.EmployeeID);
                        EmployeeAccess.Instance.GetEmployee(employee, ref employee);
                        user.EmployeeName = employee.Name;
                    }
                    lstUser.Add(user);
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        public short GetUser(User query, ref User user)
        {
            string szField = string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18}"
                 , SystemData.UserTable.Baks
                , SystemData.UserTable.ExamPlace
                , SystemData.UserTable.ExamSchool
                , SystemData.UserTable.Gender
                , SystemData.UserTable.Hotel
                , SystemData.UserTable.HotelExpense
                , SystemData.UserTable.ID
                , SystemData.UserTable.MoneyBack
                , SystemData.UserTable.Name
                , SystemData.UserTable.PassWord
                , SystemData.UserTable.PayMoney
                , SystemData.UserTable.Room
                , SystemData.UserTable.School
                , SystemData.UserTable.Sequence
                , SystemData.UserTable.Tel
                , SystemData.UserTable.EmployeeID
                , SystemData.UserTable.ExceptRoomie
                , SystemData.UserTable.Template
                , SystemData.UserTable.PayPlace);
            string szCondition = string.Format("1=1 and (status <>'0' or status is null) ");

            if (query.ID != 0)
                szCondition = string.Format("{0} and {1}={2}"
                    , szCondition
                    , SystemData.UserTable.ID, query.ID.ToString());
            if (!string.IsNullOrEmpty(query.Tel))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.UserTable.Tel, query.Tel.ToString());
            if (!string.IsNullOrEmpty(query.PassWord))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.UserTable.PassWord, query.PassWord.ToString());
            if (!string.IsNullOrEmpty(query.Hotel))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.UserTable.Hotel, query.Hotel.ToString());
            if (!string.IsNullOrEmpty(query.Room))
                szCondition = string.Format("{0} and {1}='{2}'"
                    , szCondition
                    , SystemData.UserTable.Room, query.Room.ToString());

            string szSql = string.Format("select {0} from {1} where  {2}"
                , szField
                , SystemData.DBTable.USER
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                if (ds == null || ds.Tables[0].Rows.Count <= 0)
                    return SystemData.ReturnValue.RES_NO_FOUND;
                user.ID = int.Parse(ds.Tables[0].Rows[0][SystemData.UserTable.ID].ToString());
                user.Bak = ds.Tables[0].Rows[0][SystemData.UserTable.Baks].ToString();
                user.ExamPlace = ds.Tables[0].Rows[0][SystemData.UserTable.ExamPlace].ToString();
                user.ExamSchool = ds.Tables[0].Rows[0][SystemData.UserTable.ExamSchool].ToString();
                user.Gender = ds.Tables[0].Rows[0][SystemData.UserTable.Gender].ToString();
                user.Hotel = ds.Tables[0].Rows[0][SystemData.UserTable.Hotel].ToString();
                user.HotelExpense = ds.Tables[0].Rows[0][SystemData.UserTable.HotelExpense].ToString();
                user.MoneyBack = ds.Tables[0].Rows[0][SystemData.UserTable.MoneyBack].ToString();
                user.Name = ds.Tables[0].Rows[0][SystemData.UserTable.Name].ToString();
                user.PassWord = ds.Tables[0].Rows[0][SystemData.UserTable.PassWord].ToString();
                user.PayMoney = ds.Tables[0].Rows[0][SystemData.UserTable.PayMoney].ToString();
                user.Room = ds.Tables[0].Rows[0][SystemData.UserTable.Room].ToString();
                user.School = ds.Tables[0].Rows[0][SystemData.UserTable.School].ToString();
                user.Sequence = ds.Tables[0].Rows[0][SystemData.UserTable.Sequence].ToString();
                user.Tel = ds.Tables[0].Rows[0][SystemData.UserTable.Tel].ToString();
                user.EmployeeID = ds.Tables[0].Rows[0][SystemData.UserTable.EmployeeID].ToString();
                user.ExceptRoomie = ds.Tables[0].Rows[0][SystemData.UserTable.ExceptRoomie].ToString();
                user.Template = ds.Tables[0].Rows[0][SystemData.UserTable.Template].ToString();
                user.PayPlace = ds.Tables[0].Rows[0][SystemData.UserTable.PayPlace].ToString();

            
            
                if (!string.IsNullOrEmpty(user.EmployeeID))
                {
                    Employee employee = new Employee();
                    employee.ID = int.Parse(user.EmployeeID);
                    EmployeeAccess.Instance.GetEmployee(employee, ref employee);
                    user.EmployeeName = employee.Name;
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        public short GetUser(string szID, ref User user)
        {
            if (user == null)
                user = new User();
            string szField = string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18}"
                 , SystemData.UserTable.Baks
                , SystemData.UserTable.ExamPlace
                , SystemData.UserTable.ExamSchool
                , SystemData.UserTable.Gender
                , SystemData.UserTable.Hotel
                , SystemData.UserTable.HotelExpense
                , SystemData.UserTable.ID
                , SystemData.UserTable.MoneyBack
                , SystemData.UserTable.Name
                , SystemData.UserTable.PassWord
                , SystemData.UserTable.PayMoney
                , SystemData.UserTable.Room
                , SystemData.UserTable.School
                , SystemData.UserTable.Sequence
                , SystemData.UserTable.Tel
                , SystemData.UserTable.EmployeeID
                , SystemData.UserTable.ExceptRoomie
                , SystemData.UserTable.Template
                , SystemData.UserTable.PayPlace);
            string szCondition = string.Format("1=1 and (status <>'0' or status is null) ");


            szCondition = string.Format("{0} and {1}={2}"
                , szCondition
                , SystemData.UserTable.ID, szID);


            string szSql = string.Format("select {0} from {1} where  {2}"
                , szField
                , SystemData.DBTable.USER
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                if (ds == null || ds.Tables[0].Rows.Count <= 0)
                    return SystemData.ReturnValue.RES_NO_FOUND;
                user.ID = int.Parse(ds.Tables[0].Rows[0][SystemData.UserTable.ID].ToString());
                user.Bak = ds.Tables[0].Rows[0][SystemData.UserTable.Baks].ToString();
                user.ExamPlace = ds.Tables[0].Rows[0][SystemData.UserTable.ExamPlace].ToString();
                user.ExamSchool = ds.Tables[0].Rows[0][SystemData.UserTable.ExamSchool].ToString();
                user.Gender = ds.Tables[0].Rows[0][SystemData.UserTable.Gender].ToString();
                user.Hotel = ds.Tables[0].Rows[0][SystemData.UserTable.Hotel].ToString();
                user.HotelExpense = ds.Tables[0].Rows[0][SystemData.UserTable.HotelExpense].ToString();
                user.MoneyBack = ds.Tables[0].Rows[0][SystemData.UserTable.MoneyBack].ToString();
                user.Name = ds.Tables[0].Rows[0][SystemData.UserTable.Name].ToString();
                user.PassWord = ds.Tables[0].Rows[0][SystemData.UserTable.PassWord].ToString();
                user.PayMoney = ds.Tables[0].Rows[0][SystemData.UserTable.PayMoney].ToString();
                user.Room = ds.Tables[0].Rows[0][SystemData.UserTable.Room].ToString();
                user.School = ds.Tables[0].Rows[0][SystemData.UserTable.School].ToString();
                user.Sequence = ds.Tables[0].Rows[0][SystemData.UserTable.Sequence].ToString();
                user.Tel = ds.Tables[0].Rows[0][SystemData.UserTable.Tel].ToString();
                user.EmployeeID = ds.Tables[0].Rows[0][SystemData.UserTable.EmployeeID].ToString();
                user.ExceptRoomie = ds.Tables[0].Rows[0][SystemData.UserTable.ExceptRoomie].ToString();
                user.Template = ds.Tables[0].Rows[0][SystemData.UserTable.Template].ToString();
                user.PayPlace = ds.Tables[0].Rows[0][SystemData.UserTable.PayPlace].ToString();
                if (!string.IsNullOrEmpty(user.EmployeeID))
                {
                    Employee employee = new Employee();
                    employee.ID = int.Parse(user.EmployeeID);
                    EmployeeAccess.Instance.GetEmployee(employee, ref employee);
                    user.EmployeeName = employee.Name;
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
        /// <summary>
        /// 通过手机号码作为唯一标示 查找用户解决重复导入问题
        /// </summary>
        /// <param name="szID"></param>
        /// <param name="user"></param>
        /// <returns></returns>
        public short GetUserByTel(string szTel, ref User user)
        {
            if (user == null)
                user = new User();
            string szField = string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18}"
                 , SystemData.UserTable.Baks
                , SystemData.UserTable.ExamPlace
                , SystemData.UserTable.ExamSchool
                , SystemData.UserTable.Gender
                , SystemData.UserTable.Hotel
                , SystemData.UserTable.HotelExpense
                , SystemData.UserTable.ID
                , SystemData.UserTable.MoneyBack
                , SystemData.UserTable.Name
                , SystemData.UserTable.PassWord
                , SystemData.UserTable.PayMoney
                , SystemData.UserTable.Room
                , SystemData.UserTable.School
                , SystemData.UserTable.Sequence
                , SystemData.UserTable.Tel
                , SystemData.UserTable.EmployeeID
                , SystemData.UserTable.ExceptRoomie
                , SystemData.UserTable.Template
                , SystemData.UserTable.PayPlace);
            string szCondition = string.Format("1=1  ");


            szCondition = string.Format("{0} and {1}='{2}'"
                , szCondition
                , SystemData.UserTable.Tel, szTel);


            string szSql = string.Format("select {0} from {1} where  {2}"
                , szField
                , SystemData.DBTable.USER
                , szCondition);
            try
            {
                DataSet ds = SystemParam.Instance.DBAccess.ExecuteQuery(szSql);
                if (ds == null || ds.Tables[0].Rows.Count <= 0)
                    return SystemData.ReturnValue.RES_NO_FOUND;
                user.ID = int.Parse(ds.Tables[0].Rows[0][SystemData.UserTable.ID].ToString());
                user.Bak = ds.Tables[0].Rows[0][SystemData.UserTable.Baks].ToString();
                user.ExamPlace = ds.Tables[0].Rows[0][SystemData.UserTable.ExamPlace].ToString();
                user.ExamSchool = ds.Tables[0].Rows[0][SystemData.UserTable.ExamSchool].ToString();
                user.Gender = ds.Tables[0].Rows[0][SystemData.UserTable.Gender].ToString();
                user.Hotel = ds.Tables[0].Rows[0][SystemData.UserTable.Hotel].ToString();
                user.HotelExpense = ds.Tables[0].Rows[0][SystemData.UserTable.HotelExpense].ToString();
                user.MoneyBack = ds.Tables[0].Rows[0][SystemData.UserTable.MoneyBack].ToString();
                user.Name = ds.Tables[0].Rows[0][SystemData.UserTable.Name].ToString();
                user.PassWord = ds.Tables[0].Rows[0][SystemData.UserTable.PassWord].ToString();
                user.PayMoney = ds.Tables[0].Rows[0][SystemData.UserTable.PayMoney].ToString();
                user.Room = ds.Tables[0].Rows[0][SystemData.UserTable.Room].ToString();
                user.School = ds.Tables[0].Rows[0][SystemData.UserTable.School].ToString();
                user.Sequence = ds.Tables[0].Rows[0][SystemData.UserTable.Sequence].ToString();
                user.Tel = ds.Tables[0].Rows[0][SystemData.UserTable.Tel].ToString();
                user.EmployeeID = ds.Tables[0].Rows[0][SystemData.UserTable.EmployeeID].ToString();
                user.ExceptRoomie = ds.Tables[0].Rows[0][SystemData.UserTable.ExceptRoomie].ToString();
                user.Template = ds.Tables[0].Rows[0][SystemData.UserTable.Template].ToString();
                user.PayPlace = ds.Tables[0].Rows[0][SystemData.UserTable.PayPlace].ToString();
                if (!string.IsNullOrEmpty(user.EmployeeID))
                {
                    Employee employee = new Employee();
                    employee.ID = int.Parse(user.EmployeeID);
                    EmployeeAccess.Instance.GetEmployee(employee, ref employee);
                    user.EmployeeName = employee.Name;
                }
                return SystemData.ReturnValue.OK;
            }
            catch (Exception ex)
            {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                SystemParam.Instance.Log.Error(string.Format("{0}\nErrorInfo:{1}", sf.GetMethod(), ex.ToString()));
                return SystemData.ReturnValue.EXCEPTION;
            }
        }
    }
}
